package zhuzx.report;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.Date;
import java.util.Properties;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.MimeUtility;

import org.springframework.stereotype.Service;
import org.zhuzx.util.ExcelUtil;
import org.zhuzx.util.GeneralUtil;
import org.zhuzx.util.UnifiedConstants;

@Service
public class BackupRcExportExcel {
	
	private static String[] getTimeBoundaries(String subject) {
		String start;
		String end;
		if (subject.contains("日报")) {
			return GeneralUtil.getTimestamps(-1);
		} else {
			String[] dates;
			if (subject.contains("周报")) {
				dates = GeneralUtil.getTwoDateOfLastWeek(true);
			} else if (subject.contains("月报")) {
				dates = GeneralUtil.getTwoDateOfLastMonth();
			} else {
				throw new RuntimeException(subject + "：必须在标题中声明报表的统计周期属性");
			}
			start = dates[0] + " 00:00:00";
			end = dates[1] + " 23:59:59";
		}
		return new String[] {start, end};
	}
	
	public void exportAndSend(String target, String subject, String... keys) throws Exception {
		String[] pair = getTimeBoundaries(subject);
		byte[] bytes = createExcel(keys, pair[0], pair[1]);
//		String exportFilePath = "C:\\Users\\zhuzx\\Desktop";
//		String filePath = exportFilePath + "/" + key + ".xlsx";
//		try {
//			wb.write(new FileOutputStream(filePath));
//		} catch (IOException e) {}
		ByteArrayInputStream in = new ByteArrayInputStream(bytes);
		subject += pair[0].substring(0, 10);
		if (!pair[0].substring(0,10).equals(pair[1].substring(0,10))) {
			subject += "至" + pair[1].substring(0, 10);
		}
		String content = "本邮件由系统自动发送，请勿回复。";
		sendEmail(target, subject, content, subject + ".xlsx", in);
	}
	
	public byte[] createExcel(String[] keys, String start, String end) {
		String[] sqls = new String[keys.length];
		for (int i=0; i<keys.length; i++) {
			sqls[i] = getSql(keys[i], start, end);
		}
		return ExcelUtil.createExcel(true, keys, false, sqls);
	}

	private static String getSql(String key, String start, String end) {
		InputStream in = GeneralUtil.loadFileFromClassDirectory(BackupRcExportExcel.class, key+".sql");
		String sql = new String(GeneralUtil.getBytes(in), StandardCharsets.UTF_8);
		sql = sql.replace("use fmall;", "");
		String backup = sql;
		sql = sql.replace("#{time_start}", "'"+start+"'");
		sql = sql.replace("#{time_end}", "'"+end+"'");
		sql = sql.replaceAll(">=?\\s'" + UnifiedConstants.REGEX_DATE_OR_DATETIME + "'", ">= '"+start+"'");
		sql = sql.replaceAll("<=?\\s'" + UnifiedConstants.REGEX_DATE_OR_DATETIME + "'", "<= '"+end+"'");
		if (sql.equals(backup)) {
			throw new RuntimeException(key + "：SQL语句中未找到时间范围参数");
		}
		return sql;
	}

	public static boolean sendEmail(String target, String subject, String content, String fileName, InputStream in) throws Exception {
		String from = "report@133.cn";
//		String mailserver = "smtp." + from.substring(from.indexOf('@') + 1, from.length());
		String mailserver = "smtp.qiye.163.com";
		Properties prop = new Properties();
		prop.put("mail.smtp.host", mailserver);
		prop.put("mail.smtp.auth", "true");
		Session sess = Session.getDefaultInstance(prop);
		//sess.setDebug(true);
		MimeMessage message = new MimeMessage(sess);
		message.setFrom(new InternetAddress(from));
		message.setRecipients(Message.RecipientType.TO, InternetAddress.parse(target));
		message.setSubject(subject);
		Date sendTime = new Date();
		message.setSentDate(sendTime);
		Multipart mul = new MimeMultipart("mixed");
		
		MimeBodyPart strPart = new MimeBodyPart();
		strPart.setContent(content, "text/html;charset=UTF-8");
		mul.addBodyPart(strPart);
		
		Path path = Paths.get("excel/" + fileName);
		if (!Files.exists(path.getParent())) {
			Files.createDirectory(path.getParent());
		}
		Files.deleteIfExists(path);
		Files.createFile(path);
		Files.write(path, GeneralUtil.getBytes(in));
		DataSource ds1 = new FileDataSource(path.toString());
		MimeBodyPart filePart = new MimeBodyPart();
		DataHandler dh1 = new DataHandler(ds1);
		filePart.setDataHandler(dh1);
		filePart.setFileName(MimeUtility.encodeText(fileName));
		mul.addBodyPart(filePart);
		
		//无效，文件内容为空。
//		MimeBodyPart file = new MimeBodyPart(in);
//		DataSource source = new ByteArrayDataSource(in, "application/vnd.ms-excel");
////		DataSource source = new ByteArrayDataSource(GeneralUtil.getBytes(in), "application/octet-stream");
//		file.setDataHandler(new DataHandler(source));
//		file.setFileName(MimeUtility.encodeText(fileName));
//		mul.addBodyPart(file);
		
		message.setContent(mul);
		message.saveChanges();
		Transport transport = sess.getTransport("smtp");
		transport.connect(mailserver, from, "rpr123456");
		transport.sendMessage(message, message.getAllRecipients());
		transport.close();
		System.out.println("send to below address completed: " + target);
		Files.delete(path);
		return true;
	}
	
}